from app.utils.mysql_util import get_connection

def insert_customer(customer_data):
    """插入顾客信息"""
    with get_connection() as conn:
        with conn.cursor() as cursor:
            sql = """
                INSERT INTO customers (
                    customer_no, name, mobile, email, 
                    address, level, points, created_at, 
                    updated_at, status
                ) VALUES (
                    %(customer_no)s, %(name)s, %(mobile)s, %(email)s,
                    %(address)s, %(level)s, %(points)s, NOW(),
                    NOW(), 1
                ) ON DUPLICATE KEY UPDATE
                    name = VALUES(name),
                    mobile = VALUES(mobile),
                    email = VALUES(email),
                    address = VALUES(address),
                    level = VALUES(level),
                    points = VALUES(points),
                    updated_at = NOW()
            """
            cursor.execute(sql, customer_data)
        conn.commit()

def get_customer_list(page=1, page_size=20, search_key=None):
    """获取顾客列表"""
    with get_connection() as conn:
        with conn.cursor() as cursor:
            where_clause = "WHERE status = 1"
            params = []
            
            if search_key:
                where_clause += " AND (name LIKE %s OR mobile LIKE %s OR customer_no LIKE %s)"
                search_pattern = f"%{search_key}%"
                params.extend([search_pattern, search_pattern, search_pattern])
            
            # 获取总数
            count_sql = f"SELECT COUNT(*) FROM customers {where_clause}"
            cursor.execute(count_sql, params)
            total = cursor.fetchone()[0]
            
            # 获取分页数据
            offset = (page - 1) * page_size
            sql = f"""
                SELECT customer_no, name, mobile, email, address, 
                       level, points, created_at, updated_at
                FROM customers 
                {where_clause}
                ORDER BY created_at DESC
                LIMIT %s OFFSET %s
            """
            params.extend([page_size, offset])
            cursor.execute(sql, params)
            customers = cursor.fetchall()
            
            return {
                "total": total,
                "items": customers,
                "page": page,
                "page_size": page_size
            }

def get_customer_detail(customer_no):
    """获取顾客详情"""
    with get_connection() as conn:
        with conn.cursor() as cursor:
            sql = """
                SELECT customer_no, name, mobile, email, address,
                       level, points, created_at, updated_at
                FROM customers
                WHERE customer_no = %s AND status = 1
            """
            cursor.execute(sql, (customer_no,))
            return cursor.fetchone() 